from flask import Flask, jsonify, request, render_template
from flask_cors import CORS
import pymysql.cursors
from db import get_db, close_db
from openai import OpenAI
import os
import pandas as pd
from math import pi
import util_tools
import util_tools.ai
import util_tools.datect_goods
import util_tools.leida
from util_tools.ai import RAGAssistant
from datetime import datetime
import random
# 初始化 OpenAI 客户端
client = OpenAI(
    api_key="sk-iCGKe54hp64AE9D6qBcqPh5KZbwucGBamGymqJmfc1Smiv7r",
    base_url="https://api.moonshot.cn/v1",
)

app = Flask(__name__, 
    static_folder='../static',
    template_folder='../templates')
CORS(app)  # 允许跨域请求
app.teardown_appcontext(close_db)

# ================= 页面路由 ================= #
@app.route('/')
def index():
    return render_template('index.html')

@app.route('/chat')
def chat_page():
    return render_template('chat.html')

@app.route('/lab')
def lab_page():
    return render_template('lab.html')

@app.route('/trade')
def trade_page():
    return render_template('trade.html')

@app.route('/observation')
def observation_page():
    return render_template('observation.html')

@app.route('/live')
def live_page():
    return render_template('live.html')


# ================= AI接口 ================= #
@app.route('/ask', methods=['POST'])
def handle_ai_request():
    try:
        data = request.json
        question = data.get('question', '')
        
        print(f"收到问题: {question[:50]}...")
        
        # 构建系统提示
        system_prompt = """
        你是一个专业的二手商品顾问，需要处理以下类型的咨询：
        1. 汽车类：根据上牌时间、里程数、价格等参数评估车辆价值
        2. 数码类：分析电子产品配置、使用年限、市场价格
        3. 通用建议：帮助用户判断商品性价比，识别潜在风险
        
        请基于提供的数据库信息，给出专业、易懂的分析。如果数据库中有相关信息，请优先使用这些信息。
        如果数据库中没有相关信息，请使用你的知识给出建议。
        
        对于复杂的分析，你可以使用Markdown格式来使回答更加结构化和易读。
        包括表格、列表和标题等格式。
        
        请用中文回答，保持回答简洁明了。
        """
        
        # 使用RAG助手获取回复
        rag_assistant = RAGAssistant(
            api_key="sk-iCGKe54hp64AE9D6qBcqPh5KZbwucGBamGymqJmfc1Smiv7r", 
            base_url="https://api.moonshot.cn/v1"
        )
        
        # 获取相关表数据
        relevant_tables = rag_assistant.get_relevant_tables(question, limit=5)
        table_records = []
        
        # 从每个相关表中获取最多7条记录
        for table in relevant_tables:
            records = rag_assistant.search_in_table(table, question, limit=7)
            if records:
                # 确保记录列表不为空
                table_records.append({
                    "table_name": table,
                    "columns": rag_assistant.get_table_columns(table),
                    "records": records
                })
        
        # 使用预加载的数据库上下文获取回复
        ai_response = rag_assistant.get_response_with_preloaded_context(question, system_prompt)
        
        return jsonify({
            "answer": ai_response,
            "status": "success",
            "table_records": table_records  # 添加数据库记录
        })
        
    except Exception as e:
        import traceback
        error_msg = f"AI处理错误: {str(e)}"
        print(f"{error_msg}\n{traceback.format_exc()}")
        return jsonify({
            "answer": "当前服务不可用，请稍后重试",
            "status": "error",
            "error": str(e)
        }), 500

# 搜索时生成新数据
@app.route('/generate-chart', methods=['POST'])
def generate_chart():
    data = request.json
    keyword = data.get('query', '')

    if not keyword:
        return jsonify({"error": "缺少查询参数"}), 400

    # 这里可以根据 keyword 处理数据更新逻辑
    return jsonify({"status": "success"})

# 获取雷达图数据
@app.route('/get_radar_data')
def get_radar_data():
    categories = ['价格', '过户次数', '驱动方式', '排量', '马力值', '表显里程']
    data_values = [[round(random.uniform(0, 150), 1) for _ in range(len(categories))] for _ in range(4)]
    series_names = ["Data A", "Data B", "Data C", "Data D"]

    radar_data = {
        "indicators": [{"text": category, "max": 150} for category in categories],
        "series": [{"name": series_names[i], "value": data_values[i]} for i in range(len(series_names))]
    }
    print(jsonify(radar_data))
    return jsonify(radar_data)

# ================= 上传图片 ================= #
@app.route('/upload_image', methods=['POST'])
def upload_image():
    if 'image' not in request.files:
        return jsonify({"error": "未检测到图片"}), 400

    file = request.files['image']
    file.save(f"static/uploads/{file.filename}")  # 保存图片（可选）
    goods_name = util_tools.datect_goods.detect_objects(f"static/uploads/{file.filename}")
    if goods_name :
        return jsonify({"result": goods_name})  # 返回 JSON
    else:
        return jsonify({"error": "请重新上传"}), 400

# ================= 更新图表 ================= #
@app.route('/api/tables', methods=['GET'])
def get_tables():
    """获取所有数据库表名"""
    try:
        db = get_db()
        if not db:
            return jsonify({"error": "数据库连接失败"}), 500
            
        cursor = db.cursor()
        cursor.execute("SHOW TABLES")
        tables = [table[0] for table in cursor.fetchall()]
        cursor.close()
        
        return jsonify({
            "status": "success",
            "tables": tables
        })
    except Exception as e:
        import traceback
        error_msg = f"获取表名错误: {str(e)}"
        print(f"{error_msg}\n{traceback.format_exc()}")
        return jsonify({
            "status": "error",
            "message": error_msg
        }), 500

@app.route('/api/table/<table_name>', methods=['GET'])
def get_table_data(table_name):
    """获取指定表的数据"""
    try:
        # 获取分页参数
        page = request.args.get('page', 1, type=int)
        page_size = request.args.get('page_size', 10, type=int)
        
        # 计算偏移量
        offset = (page - 1) * page_size
        
        db = get_db()
        if not db:
            return jsonify({"error": "数据库连接失败"}), 500
            
        cursor = db.cursor(pymysql.cursors.DictCursor)  # 使用字典游标
        
        # 获取表的总行数
        cursor.execute(f"SELECT COUNT(*) as count FROM `{table_name}`")
        total = cursor.fetchone()['count']
        
        # 获取表数据
        cursor.execute(f"SELECT * FROM `{table_name}` LIMIT %s OFFSET %s", (page_size, offset))
        rows = cursor.fetchall()
        
        # 获取表结构信息
        cursor.execute(f"DESCRIBE `{table_name}`")
        columns = [column['Field'] for column in cursor.fetchall()]
        
        cursor.close()
        
        return jsonify({
            "status": "success",
            "data": rows,
            "total": total,
            "columns": columns,
            "page": page,
            "page_size": page_size,
            "total_pages": (total + page_size - 1) // page_size
        })
    except Exception as e:
        import traceback
        error_msg = f"获取表数据错误: {str(e)}"
        print(f"{error_msg}\n{traceback.format_exc()}")
        return jsonify({
            "status": "error",
            "message": error_msg
        }), 500

@app.route('/api/search/<table_name>', methods=['GET'])
def search_table(table_name):
    """搜索表数据"""
    try:
        # 获取查询参数
        keyword = request.args.get('keyword', '')
        page = request.args.get('page', 1, type=int)
        page_size = request.args.get('page_size', 10, type=int)
        
        if not keyword:
            return jsonify({"error": "请提供搜索关键词"}), 400
            
        # 计算偏移量
        offset = (page - 1) * page_size
        
        db = get_db()
        if not db:
            return jsonify({"error": "数据库连接失败"}), 500
            
        cursor = db.cursor(pymysql.cursors.DictCursor)
        
        # 获取表的所有列
        cursor.execute(f"DESCRIBE `{table_name}`")
        columns = [column['Field'] for column in cursor.fetchall()]
        
        # 构建搜索查询
        search_conditions = " OR ".join([f"`{col}` LIKE %s" for col in columns])
        search_query = f"SELECT * FROM `{table_name}` WHERE {search_conditions} LIMIT %s OFFSET %s"
        
        # 准备参数
        params = [f"%{keyword}%"] * len(columns) + [page_size, offset]
        
        # 执行搜索
        cursor.execute(search_query, params)
        results = cursor.fetchall()
        
        # 获取匹配的总行数
        count_query = f"SELECT COUNT(*) as count FROM `{table_name}` WHERE {search_conditions}"
        count_params = [f"%{keyword}%"] * len(columns)
        cursor.execute(count_query, count_params)
        total = cursor.fetchone()['count']
        
        cursor.close()
        
        return jsonify({
            "status": "success",
            "data": results,
            "total": total,
            "page": page,
            "page_size": page_size,
            "total_pages": (total + page_size - 1) // page_size
        })
    except Exception as e:
        import traceback
        error_msg = f"搜索表数据错误: {str(e)}"
        print(f"{error_msg}\n{traceback.format_exc()}")
        return jsonify({
            "status": "error",
            "message": error_msg
        }), 500

@app.route('/status', methods=['GET'])
def api_status():
    """API状态检查接口"""
    return jsonify({
        "status": "online",
        "message": "AI服务正常运行中",
        "time": datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    })


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=3000, debug=True)
